<?php
class Pembagian_kelas extends CI_Model 
{

	function __construct()
	{
		parent::__construct();
	}

	function detail($id_mr_th_ajaran,$id_lembaga)
	{
		$this->db->select('id_atur_kelas, mr_th_ajaran.*, mr_kelas.*, mr_pegawai.id_mr_pegawai, nm_pegawai, (select count(nis) from bagi_kelas where id_atur_kelas = atur_kelas.id_atur_kelas ) as jml');
		$this->db->from('atur_kelas');
		$this->db->join('mr_th_ajaran', 'mr_th_ajaran.id_mr_th_ajaran = atur_kelas.id_mr_th_ajaran');
		$this->db->join('mr_kelas', 'mr_kelas.id_mr_kelas = atur_kelas.id_mr_kelas');
		$this->db->join('mr_pegawai', 'mr_pegawai.id_mr_pegawai = atur_kelas.id_mr_pegawai');
		$this->db->where('mr_th_ajaran.id_mr_th_ajaran', $id_mr_th_ajaran);
		$this->db->where('id_lembaga', $id_lembaga);
		$this ->db->order_by('kelas');

		$query = $this->db->get();		
		return $query->result();
	}
	
	function siswa($id_atur_kelas)
	{
		$this->db->select('mr_siswa.*,kelas,atur_kelas.id_atur_kelas,nm_pegawai,th_ajaran,id_bagi_kelas');
		$this->db->from('bagi_kelas');
		$this->db->join('mr_siswa', 'mr_siswa.nis = bagi_kelas.nis');
		$this->db->join('atur_kelas', 'atur_kelas.id_atur_kelas = bagi_kelas.id_atur_kelas');
		$this->db->join('mr_kelas', 'mr_kelas.id_mr_kelas = atur_kelas.id_mr_kelas');
		$this->db->join('mr_pegawai', 'mr_pegawai.id_mr_pegawai = atur_kelas.id_mr_pegawai');
		$this->db->join('mr_th_ajaran', 'mr_th_ajaran.id_mr_th_ajaran = atur_kelas.id_mr_th_ajaran');
		$this->db->where('atur_kelas.id_atur_kelas', $id_atur_kelas);
		$this ->db->order_by('mr_siswa.nis');

		$query = $this->db->get();		
		return $query->result();
	}
	
	
	function meta($id_atur_kelas)
	{
		$this->db->select('atur_kelas.id_mr_th_ajaran,kelas,th_ajaran,nm_pegawai');
		$this->db->from('atur_kelas');
		$this->db->join('mr_kelas', 'mr_kelas.id_mr_kelas = atur_kelas.id_mr_kelas');
		$this->db->join('mr_th_ajaran', 'mr_th_ajaran.id_mr_th_ajaran = atur_kelas.id_mr_th_ajaran');
		$this->db->join('mr_pegawai', 'mr_pegawai.id_mr_pegawai = atur_kelas.id_mr_pegawai');
		$this->db->where('id_atur_kelas', $id_atur_kelas);

		$query = $this->db->get();		
		return $query->result();
	}
	
	function siswa_baru($id_lembaga)
	{
		$this->db->select('nis,nm_lengkap');
		$this->db->from('mr_siswa');
		$this->db->where('id_lembaga',$id_lembaga);
		$this->db->where('nis not in (select nis from bagi_kelas join atur_kelas on atur_kelas.id_atur_kelas = bagi_kelas.id_atur_kelas where id_lembaga = '.$id_lembaga.')');
		$this ->db->order_by('nis');

		$query = $this->db->get();		
		return $query->result();
	}
	
	function list_siswa($id_mr_kelas,$id_mr_th_ajaran,$id_atur_kelas,$id_lembaga)
	{
		$this->db->select('bagi_kelas.id_bagi_kelas,mr_siswa.nis, nm_lengkap, mr_kelas.id_mr_kelas, kelas, mr_th_ajaran.id_mr_th_ajaran, th_ajaran');
		$this->db->from('atur_kelas');
		$this->db->join('bagi_kelas','bagi_kelas.id_atur_kelas = atur_kelas.id_atur_kelas');
		$this->db->join('mr_siswa','mr_siswa.nis = bagi_kelas.nis');
		$this->db->join('mr_kelas','mr_kelas.id_mr_kelas = atur_kelas.id_mr_kelas');
		$this->db->join('mr_th_ajaran','mr_th_ajaran.id_mr_th_ajaran = atur_kelas.id_mr_th_ajaran');
		$this->db->where('atur_kelas.id_mr_kelas',$id_mr_kelas);
		$this->db->where('atur_kelas.id_mr_th_ajaran',$id_mr_th_ajaran);
		$this->db->where('bagi_kelas.id_atur_kelas !=',$id_atur_kelas);		
		$this->db->where('mr_siswa.id_lembaga',$id_lembaga);		
		$this->db->where('bagi_kelas.nis not in (
			SELECT nis
			FROM bagi_kelas
			WHERE id_atur_kelas = '.$id_atur_kelas.')');		
		$this ->db->order_by('mr_siswa.nis');

		$query = $this->db->get();		
		return $query->result();
	}	

	function add($dataInsert)
	{
		$query = $this->db->insert('bagi_kelas', $dataInsert);
		return $query;
	}
	
	function delete($id_bagi_kelas)
	{
		$this->db->where('id_bagi_kelas', $id_bagi_kelas);
		$delete = $this->db->delete('bagi_kelas');
		return $delete;
	}
	
	function th_lembaga($id_lembaga)
	{
		$this->db->select('distinct(atur_kelas.id_mr_th_ajaran),th_ajaran');
		$this->db->from('atur_kelas');
		$this->db->join('mr_th_ajaran','mr_th_ajaran.id_mr_th_ajaran = atur_kelas.id_mr_th_ajaran');
		$this->db->where('id_lembaga', $id_lembaga);
		$this->db->order_by('mr_th_ajaran.id_mr_th_ajaran','desc');

		$query = $this->db->get();		
		return $query->result();
	}

	function kelas_lembaga($id_lembaga)
	{
		$this->db->select('distinct(atur_kelas.id_mr_kelas),kelas');
		$this->db->from('atur_kelas');
		$this->db->join('mr_kelas','mr_kelas.id_mr_kelas = atur_kelas.id_mr_kelas');
		$this->db->where('id_lembaga', $id_lembaga);
		$this->db->order_by('mr_kelas.id_mr_kelas');

		$query = $this->db->get();		
		return $query->result();
	}

	function kelas_pindah($id_lembaga,$id_atur_kelas,$id_mr_th_ajaran)
	{
		$this->db->select('distinct(atur_kelas.id_mr_kelas),kelas');
		$this->db->from('atur_kelas');
		$this->db->join('mr_kelas','mr_kelas.id_mr_kelas = atur_kelas.id_mr_kelas');
		$this->db->where('id_lembaga', $id_lembaga);
		$this->db->where('id_atur_kelas !=', $id_atur_kelas);
		$this->db->where('atur_kelas.id_mr_th_ajaran',$id_mr_th_ajaran);
		$this->db->order_by('mr_kelas.id_mr_kelas');

		$query = $this->db->get();		
		return $query->result();
	}

	function pindah($dataUpdate,$id_bagi_kelas)
	{
		$this->db->where('id_bagi_kelas', $id_bagi_kelas);
		$update = $this->db->update('bagi_kelas', $dataUpdate);
		return $update;
	}
}